﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmCorporateProductionApprovalFinancial

    Dim FolderPath As String = FTPAddress + "Production Corporate"


    Sub FillPendingTransactions()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select SNo,ClientNo,ClientName,EndorsType,SavedUser,SavedDate,ApprovedUser,ApprovedDate " & _
                                      "From Policies Where Done=1 and DoneFin=0 and Branch=N'" & Branch & "'", cnn)
            Dim Reader As SqlDataReader
            Dim X As Integer = 1

            Clear()
            Me.ListPendingTrans.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("SNo")
                Item.SubItems.Add(X)
                Item.SubItems.Add(Reader.Item("ClientNo"))
                Item.SubItems.Add(Reader.Item("ClientName"))
                Item.SubItems.Add(Reader.Item("EndorsType"))
                Item.SubItems.Add(Reader.Item("SavedUser"))
                Item.SubItems.Add(CDate(Reader.Item("SavedDate")).ToString("dd-MMM-yyyy HH:mm"))
                Item.SubItems.Add(Reader.Item("ApprovedUser"))
                Item.SubItems.Add(CDate(Reader.Item("ApprovedDate")).ToString("dd-MMM-yyyy HH:mm"))

                Me.ListPendingTrans.Items.Add(Item)
                X += 1
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillProducers()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim ProducersList As ArrayList = GetProducersList()

            Me.CombProducers.Items.Clear()

            For Each Producer As String In ProducersList
                Me.CombProducers.Items.Add(Producer)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.ErrProv.Clear()
        Me.txtNo.Clear()
        Me.txtClientNo.Clear()
        Me.txtClientName.Clear()
        Me.RDirectBusiness.Checked = True
        Me.CombDirectBusiness.SelectedIndex = -1
        Me.txtAccNo.Clear()
        Me.txtPolicyNo.Clear()
        Me.txtType.Clear()
        Me.txtEndorsType.Clear()
        Me.txtEndorsNo.Clear()
        Me.txtInsurComp.Clear()
        Me.txtInsurClasses.Clear()
        Me.txtIssueDate.Clear()
        Me.txtPeriodFRm.Clear()
        Me.txtSumInsur.Clear()
        Me.txtClientDNCNNo.Clear()
        Me.txtTotalPremium.Clear()
        Me.txtNetPremium.Clear()
        Me.txtCompDNCNNo.Clear()
        Me.txtCompComm.Clear()
        Me.CombProducers.SelectedIndex = -1
        Me.txtProdCommPerc.Value = 0
        Me.txtProdCommAmount.Text = "0.00"
        Me.GridPaymentsSchedule.Rows.Clear()
        Me.CombProducers.SelectedIndex = -1
        Me.txtProdCommPerc.Value = 0
        Me.txtProdCommAmount.Text = "0.00"
        Me.GridProducersCommissions.Rows.Clear()
        Me.GridVoucher.Rows.Clear()
        Me.GridFiles.Rows.Clear()
    End Sub

    Function CheckPolicyNo(ByVal PolNo As String) As Boolean
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Policies Where PolicyNo=N'" & PolNo & "'", cnn)
            Dim Count As Integer

            cnn.Open()
            Count = CInt(cmd.ExecuteScalar)
            cnn.Close()

            If Count > 0 Then
                Me.Cursor = Cursors.Default
                Return True
            Else
                Me.Cursor = Cursors.Default
                Return False
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

#Region "Fill Policy Details"

    Sub FillPaymentSchedule()
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim cmd As New SqlCommand("Select * From PaymentsSchedule Where TransNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
            Dim Reader As SqlDataReader

            Me.GridPaymentsSchedule.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.GridPaymentsSchedule.Rows.Add(New String() {CDate(Reader.Item("PayDate")).ToString("dd/MM/yyyy"), _
                                                              CDbl(Reader.Item("Amount")).ToString("N2"), "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub FillDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FolderPath & "\" & Me.ListPendingTrans.SelectedItems(0).Text)
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            ''list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.FullName, Fi.Name, "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

#End Region
    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombDirectBusiness.Items.Clear()
            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombDirectBusiness.Items.Add("Direct Business - " & Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmApprove_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillBranches()
        FillProducers()
        FillPendingTransactions()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub GridPaymentsSchedule_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridPaymentsSchedule.CellClick
        If e.ColumnIndex = 2 Then
            If MsgBox("Confirm Delete?", MsgBoxStyle.YesNo, Me.Text) = MsgBoxResult.Yes Then
                Me.GridPaymentsSchedule.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub txtPolicyNo_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPolicyNo.Leave
        If Me.txtPolicyNo.Text.Trim.Length > 0 Then
            If CheckPolicyNo(Me.txtPolicyNo.Text.Trim) = True Then
                Me.ErrProv.SetError(Me.txtPolicyNo, "Policy no. already exists")
            Else
                Me.ErrProv.Clear()
            End If
        End If
    End Sub

    Private Sub txtPolicyNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPolicyNo.TextChanged
        Me.ErrProv.Clear()
    End Sub

    Private Sub txtEndorsNo_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtEndorsNo.Leave
        If Me.txtEndorsNo.Text.Trim.Length > 0 Then
            If CheckEndorsNo(Me.txtPolicyNo.Text.Trim, Me.txtEndorsNo.Text.Trim) = True Then
                Me.ErrProv.SetError(Me.txtEndorsNo, "Endorsement no. already exists")
            Else
                Me.ErrProv.Clear()
            End If
        End If
    End Sub

    Function CheckEndorsNo(ByVal PolicyNo As String, ByVal EndorsNo As String) As Boolean
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Policies Where PolicyNo=N'" & PolicyNo & "' and EndorsNo=N'" & EndorsNo & "'", cnn)
            Dim Count As Integer

            cnn.Open()
            Count = CInt(cmd.ExecuteScalar)
            cnn.Close()

            If Count > 0 Then
                Me.Cursor = Cursors.Default
                Return True
            Else
                Me.Cursor = Cursors.Default
                Return False
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Sub CalculateProdComm()
        Try
            For Each row As DataGridViewRow In Me.GridProducersCommissions.Rows
                row.Cells(Me.DGColProdComm.Index).Value = CDbl(CDbl(Me.txtCompComm.Text) * (row.Cells(Me.DGColProdPerc.Index).Value) / 100).ToString("N2")
            Next
        Catch ex As Exception

        End Try
    End Sub

    Private Sub RDirectBusiness_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDirectBusiness.CheckedChanged
        CheckRadioProducer()
    End Sub

    Private Sub RProducer_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RProducer.CheckedChanged
        CheckRadioProducer()
    End Sub

    Private Sub txtProdCommPerc_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtProdCommPerc.ValueChanged
        Try
            Me.txtProdCommAmount.Text = CDbl(CDbl(Me.txtCompComm.Text) * Me.txtProdCommPerc.Value / 100).ToString("N2")
        Catch ex As Exception
            Me.txtProdCommAmount.Text = "0.00"
        End Try
    End Sub

    Private Sub btnAddProdComm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddProdComm.Click
        Me.ErrProv.Clear()
        If Me.CombProducers.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombProducers, "Please fill in all details")
            Me.CombProducers.Focus()

        ElseIf CDbl(Me.txtProdCommAmount.Text) = 0 Then
            Me.ErrProv.SetError(Me.txtProdCommPerc, "Please fill in all details")
            Me.txtProdCommPerc.Focus()

        Else
            Try
                Me.GridProducersCommissions.Rows.Add({Me.CombProducers.SelectedItem, Me.txtProdCommPerc.Value, Me.txtProdCommAmount.Text})

                BuildVoucher()

                Me.txtProdCommPerc.Value = 0
                Me.txtProdCommAmount.Text = "0.00"
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If
    End Sub

    Private Sub txtPolicyNo_MouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles txtPolicyNo.MouseDoubleClick
        If Me.txtPolicyNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPolicyNo, "Please fill in")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim dap As New SqlDataAdapter("Select * From Policies Where PolicyNo=N'" & Me.txtPolicyNo.Text.Trim & "'", cnn)
                Dim das As New DataSet

                cnn.Open()
                dap.Fill(das, "Policies")
                cnn.Close()

                Dim rpt As New ListOfPolicies
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.CrystalReportViewer1.RefreshReport()
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridProducersCommissions_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProducersCommissions.CellClick
        If e.ColumnIndex = Me.DGColProdDel.Index Then
            Me.GridProducersCommissions.Rows.RemoveAt(e.RowIndex)

            BuildVoucher()
        End If
    End Sub

    Sub CheckRadioProducer()
        If Me.RDirectBusiness.Checked = True Then
            Me.CombDirectBusiness.Enabled = True
            Me.CombProducers.SelectedIndex = -1
            Me.CombProducers.Enabled = False
            Me.txtProdCommPerc.Value = 0
            Me.txtProdCommPerc.Enabled = False
            Me.btnAddProdComm.Enabled = False
            Me.GridProducersCommissions.Rows.Clear()

        ElseIf Me.RProducer.Checked = True Then
            Me.CombDirectBusiness.Enabled = False
            Me.CombDirectBusiness.SelectedIndex = -1
            Me.CombProducers.Enabled = True
            Me.txtProdCommPerc.Enabled = True
            Me.btnAddProdComm.Enabled = True
        End If
    End Sub

    Private Sub ListPendingTrans_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListPendingTrans.SelectedIndexChanged
        Try
            Clear()
            If Me.ListPendingTrans.SelectedItems.Count > 0 Then
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select * From Policies Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While (Reader.Read)
                    'Check Producer
                    If Me.GridProducersCommissions.Rows.Count = -1 Then
                        Me.RDirectBusiness.Checked = True
                        Me.CombProducers.Enabled = False
                    Else
                        Me.RProducer.Checked = True
                        Me.CombProducers.Enabled = True
                    End If
                    'Me.txtNo.Text = Reader.Item("PolEndNo")
                    Me.txtClientNo.Text = Reader.Item("ClientNo")
                    Me.txtClientName.Text = Reader.Item("ClientName")
                    Me.txtProducer.Text = Reader.Item("Producer")
                    Me.txtAccNo.Text = Reader.Item("AccNo")
                    Me.txtPolicyNo.Text = Reader.Item("PolicyNo")
                    Me.txtType.Text = "Policy"
                    Me.txtEndorsType.Text = Reader.Item("EndorsType")
                    Me.txtEndorsNo.Text = Reader.Item("EndorsNo")
                    Me.txtInsurComp.Text = Reader.Item("InsurComp")
                    Me.txtInsurClasses.Text = Reader.Item("ClassName")
                    Me.txtLineofBusiness.Text = Reader.Item("LineOfBusiness")
                    Me.txtIssueDate.Text = Reader.Item("IssueDate")
                    Me.txtPeriodFRm.Text = Reader.Item("PeriodFrom")
                    Me.txtPeriodTo.Text = Reader.Item("PeriodTo")

                    Me.txtSumInsur.Text = CDbl(Reader.Item("SumInsur")).ToString("N2")
                    Me.txtClientDNCNNo.Text = Reader.Item("ClientDNCNNo")
                    Me.txtTotalPremium.Text = CDbl(Reader.Item("TotalPremium")).ToString("N2")
                    Me.txtNetPremium.Text = CDbl(Reader.Item("NetPremium")).ToString("N2")
                    Me.txtCompDNCNNo.Text = Reader.Item("CompCommDNCNNo")
                    Me.txtCompComm.Text = CDbl(Reader.Item("CompComm")).ToString("N2")
                End While
                Reader.Close()
                cnn.Close()

                FillPaymentSchedule()
                FillDocuments()
                BuildVoucher()
                Calculate()
                ValidateGrid()

                Me.ListPendingTrans.Focus()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub BuildVoucher()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.GridVoucher.Rows.Clear()

            Dim ClientAccNo, InsurCompPremAccNo, InsurCompAccruedCommissionAccNo, RevCommissionAccNo, ProducerAccNo, ProducerExpensesAccNo, SamaAccNo, SamaAccNoExpenses As String
            Dim AccNames(3), DescrPrem, DescrComm As String
            Dim Premium, CompanyCommission, SAMASupervFees, ProducerComm As Double

            Premium = Math.Abs(CDbl(Me.txtTotalPremium.Text)).ToString("N2")
            CompanyCommission = Math.Abs(CDbl(Me.txtCompComm.Text)).ToString("N2")
            SAMASupervFees = CompanyCommission * 1 / 100

            ClientAccNo = GetClientAccNo(CInt(Me.txtClientNo.Text))
            InsurCompPremAccNo = GetInsurCompPremAccNo(Me.txtInsurComp.Text)
            InsurCompAccruedCommissionAccNo = GetInsurCompAccruedCommAccNo(Me.txtInsurComp.Text)
            RevCommissionAccNo = GetInsurClassAccNo(Me.txtInsurClasses.Text)
            SamaAccNo = GetFinancialSettingsAccNo("SAMA Supervisory Fees")
            SamaAccNoExpenses = GetFinancialSettingsAccNo("SAMA Supervisory Fees - Expenses")
            ProducerExpensesAccNo = GetFinancialSettingsAccNo("Producers Commissions - Expenses")

            If CDbl(Me.txtTotalPremium.Text) > 0 Then
                If Me.txtEndorsNo.Text.Trim.Length = 0 Then
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", DN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", CN# " & Me.txtCompDNCNNo.Text.Trim
                Else
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", DN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", CN# " & Me.txtCompDNCNNo.Text.Trim
                End If
            Else
                If Me.txtEndorsNo.Text.Trim.Length = 0 Then
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", CN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", DN# " & Me.txtCompDNCNNo.Text.Trim
                Else
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", CN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", DN# " & Me.txtCompDNCNNo.Text.Trim
                End If
            End If

            '1) Client vs Insurance Company Premium
            If CDbl(Me.txtTotalPremium.Text) > 0 Then
                AccNames = GetAccNames(ClientAccNo)
                Me.GridVoucher.Rows.Add({ClientAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         Premium.ToString("N2"), "0.00"})

                AccNames = GetAccNames(InsurCompPremAccNo)
                Me.GridVoucher.Rows.Add({InsurCompPremAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         "0.00", Premium.ToString("N2")})
            Else
                AccNames = GetAccNames(ClientAccNo)
                Me.GridVoucher.Rows.Add({ClientAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         "0.00", Premium.ToString("N2")})

                AccNames = GetAccNames(InsurCompPremAccNo)
                Me.GridVoucher.Rows.Add({InsurCompPremAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         Premium.ToString("N2"), "0.00"})
            End If

            '2) Insurance Company vs Commission
            If CDbl(Me.txtCompComm.Text) > 0 Then
                AccNames = GetAccNames(InsurCompAccruedCommissionAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccruedCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         CompanyCommission.ToString("N2"), "0.00"})

                AccNames = GetAccNames(RevCommissionAccNo)
                Me.GridVoucher.Rows.Add({RevCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", CompanyCommission.ToString("N2")})
            Else
                AccNames = GetAccNames(InsurCompAccruedCommissionAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccruedCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", CompanyCommission.ToString("N2")})

                AccNames = GetAccNames(RevCommissionAccNo)
                Me.GridVoucher.Rows.Add({RevCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         CompanyCommission.ToString("N2"), "0.00"})
            End If

            '3) Commission vs SAMA Supervisory Fees
            If CDbl(Me.txtCompComm.Text) > 0 Then
                AccNames = GetAccNames(SamaAccNoExpenses)
                Me.GridVoucher.Rows.Add({SamaAccNoExpenses, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         SAMASupervFees.ToString("N2"), "0.00"})

                AccNames = GetAccNames(SamaAccNo)
                Me.GridVoucher.Rows.Add({SamaAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", SAMASupervFees.ToString("N2")})
            Else
                AccNames = GetAccNames(SamaAccNoExpenses)
                Me.GridVoucher.Rows.Add({SamaAccNoExpenses, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", SAMASupervFees.ToString("N2")})

                AccNames = GetAccNames(SamaAccNo)
                Me.GridVoucher.Rows.Add({SamaAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         SAMASupervFees.ToString("N2"), "0.00"})
            End If

            '4) Commission vs Producers Commission
            For Each row As DataGridViewRow In Me.GridProducersCommissions.Rows
                ProducerAccNo = GetProducerAccNo(row.Cells(Me.DGColProducer.Index).Value)
                ProducerComm = Math.Abs(CDbl(row.Cells(Me.DGColProdComm.Index).Value))
                If CDbl(row.Cells(Me.DGColProdComm.Index).Value) > 0 Then
                    AccNames = GetAccNames(ProducerExpensesAccNo)
                    Me.GridVoucher.Rows.Add({ProducerExpensesAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             ProducerComm.ToString("N2"), "0.00"})

                    AccNames = GetAccNames(ProducerAccNo)
                    Me.GridVoucher.Rows.Add({ProducerAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             "0.00", ProducerComm.ToString("N2")})
                Else
                    AccNames = GetAccNames(ProducerExpensesAccNo)
                    Me.GridVoucher.Rows.Add({ProducerExpensesAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             "0.00", ProducerComm.ToString("N2")})

                    AccNames = GetAccNames(ProducerAccNo)
                    Me.GridVoucher.Rows.Add({ProducerAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             ProducerComm.ToString("N2"), "0.00"})
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = Me.DGFilesColDocOpen.Index Then
            Try
                Me.Cursor = Cursors.WaitCursor

                System.Diagnostics.Process.Start(Me.GridFiles.CurrentRow.Cells(Me.DGFilesPath.Index).Value)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnApprove.Click
        Me.ErrProv.Clear()
        If Me.ListPendingTrans.SelectedItems.Count = 0 Then
            MsgBox("Please select a policy/endorsement from the list")
            Exit Sub

        ElseIf Me.RDirectBusiness.Checked = True And Me.CombDirectBusiness.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombDirectBusiness, "Please select a valid producer from the list")

        ElseIf Me.RProducer.Checked = True And Me.GridProducersCommissions.Rows.Count = -1 Then
            Me.ErrProv.SetError(Me.CombProducers, "Please select a valid producer from the list")

        ElseIf Me.GridVoucher.Rows.Count = 0 Then
            MsgBox("Please enter voucher details")

        ElseIf CDbl(Me.txtCrd.Text - Me.txtDep.Text) <> 0 Then
            Me.ErrProv.SetError(Me.txtBalance, "Please complete the voucher")
            
        ElseIf ValidateDate(Me.DTPTrans.Value) = False Then
            Me.ErrProv.SetError(Me.DTPTrans, "Date isn't accepted")

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Me.Cursor = Cursors.WaitCursor

                'Validate rows
                Dim Valid As Boolean = True
                For Each row As DataGridViewRow In Me.GridVoucher.Rows
                    If row.Index <> (Me.GridVoucher.Rows.Count - 1) Then
                        If row.Cells(4).Style.BackColor = Color.Salmon OrElse row.Cells(6).Value = "" _
                      OrElse row.Cells(7).Value = "" OrElse row.Cells(8).Value = "" OrElse row.Cells(9).Style.BackColor = Color.Salmon Then
                            row.ErrorText = "Please fill in all fields"
                            Valid = False
                        Else
                            row.ErrorText = ""
                        End If
                    End If
                Next

                If Valid = False Then
                    Me.Cursor = Cursors.Default
                    Exit Sub
                End If

                Dim MoveNo As Integer
                Dim DocSNo, Year As Integer
                Dim IssueType, Abbreviation, Pattern, PatternType As String
                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                cmd.Connection = cnn
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Get Year
                cmd.CommandText = "Select Year(GetDate())"
                Year = CInt(cmd.ExecuteScalar)

                'The Creation of the Pattern

                'Get Abbreviation
                Abbreviation = GetAbbreviation(Me.txtInsurClasses.Text)

                'Get Doc SNo
                If Me.txtType.Text = "Policy" Then
                    PatternType = "POL"
                    cmd.CommandText = "Select IsNull(Max(DocSNo),10000) From Policies Where Year(TransDate)=Year(GetDate()) and " & _
                                      "ClassName=@ClassName and EndorsType=N'Policy' and Branch=N'" & Branch & "'"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@ClassName", Me.txtInsurClasses.Text)

                Else
                    PatternType = "END"
                    cmd.CommandText = "Select IsNull(Max(DocSNo),10000) From Policies Where Year(TransDate)=Year(GetDate()) and " & _
                                      "ClassName=@ClassName and EndorsType<>N'Policy' and Branch=N'" & Branch & "'"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@ClassName", Me.txtInsurClasses.Text)
                End If
                DocSNo = CInt(cmd.ExecuteScalar) + 1

                'Create Pattern 
                Pattern = PatternType & "-" & BranchCode & "-" & Year & "-" & Abbreviation & "-" & DocSNo

                'Get Move No.
                cmd.CommandText = "Select IsNull(Max(MoveNo),0) From Transactions Where Branch=N'" & Branch & "' and Year(TransDate)=" & Me.DTPTrans.Value.Year
                MoveNo = CInt(cmd.ExecuteScalar) + 1

                For Each row As DataGridViewRow In Me.GridVoucher.Rows
                    If row.Index <> (Me.GridVoucher.Rows.Count - 1) Then
                        cmd.CommandText = "Insert into Transactions (TransType,MoveNo,AccNo,Acc1,Acc2,Acc3,Acc4,Descr,TotalOut,TotalIn," & _
                                          "SavedUser,TransDate,Branch,ApprovedUser,ApprovedDate) Values (@TransType,@MoveNo,@AccNo,@Acc1," & _
                                          "@Acc2,@Acc3,@Acc4,@Descr,@TotalOut,@TotalIn,@SavedUser,@TransDate,@Branch,@ApprovedUser,GetDate())"

                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@TransType", "Journal Voucher")
                        cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                        cmd.Parameters.AddWithValue("@AccNo", row.Cells(Me.DGColAccNo.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc1", row.Cells(Me.DGColAcc1.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc2", row.Cells(Me.DGColAcc2.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc3", row.Cells(Me.DGColAcc3.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc4", row.Cells(Me.DGColAcc4.Index).Value)
                        cmd.Parameters.AddWithValue("@Descr", row.Cells(Me.DGColDescr.Index).Value)
                        cmd.Parameters.AddWithValue("@Branch", Branch)
                        cmd.Parameters.AddWithValue("@TotalOut", CDbl(row.Cells(Me.DGColDebit.Index).Value))
                        cmd.Parameters.AddWithValue("@TotalIn", CDbl(row.Cells(Me.DGColCredit.Index).Value))
                        cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                        cmd.Parameters.AddWithValue("@TransDate", Me.DTPTrans.Value)
                        cmd.Parameters.AddWithValue("@ApprovedUser", CurrentUser)
                        cmd.ExecuteNonQuery()
                    End If
                Next

                'Update Pol/End
                cmd.CommandText = "Update Policies Set DoneFin=1,PolEndNo=@PolEndNo,FinApprovedUser=@FinApprovedUser,FinApprovedDate=GetDate() " & _
                                   "Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@PolEndNo", Pattern)
                cmd.Parameters.AddWithValue("@FinApprovedUser", CurrentUser)
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                MsgBox("Approved Successfully" & Chr(13) & "File No. " & Pattern)


                PrintVoucher(Branch, MoveNo, Me.DTPTrans.Value.Year)

                FillPendingTransactions()
                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnRefreshPendTrans_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshPendTrans.Click
        FillPendingTransactions()
    End Sub

    Private Sub ChAccPath_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChAccPath.CheckedChanged
        If Me.ChAccPath.Checked = True Then
            Me.DGColAcc1.Visible = True
            Me.DGColAcc2.Visible = True
            Me.DGColAcc3.Visible = True
        ElseIf Me.ChAccPath.Checked = False Then
            Me.DGColAcc1.Visible = False
            Me.DGColAcc2.Visible = False
            Me.DGColAcc3.Visible = False
        End If
    End Sub

    Private Sub GridVoucher_CellClick_1(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridVoucher.CellClick
        If e.ColumnIndex = Me.DGColSearch.Index Then
            Try
                Dim a As New frmSelectAccount
                a.ShowDialog()

                If SelAccNo <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(SelAccNo)

                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAccNo.Index).Value = SelAccNo
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = AccNames(0)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = AccNames(1)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = AccNames(2)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColDelete.Index Then
            If e.RowIndex < (Me.GridVoucher.Rows.Count - 1) Then
                Me.GridVoucher.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub GridVoucher_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridVoucher.CellEndEdit
        If e.ColumnIndex = Me.DGColAccNo.Index Then
            Try
                If Me.GridVoucher.CurrentCell.Value <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(Me.GridVoucher.CurrentCell.Value)

                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = AccNames(0)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = AccNames(1)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = AccNames(2)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                Else
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = ""
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColDebit.Index Then
            If Me.GridVoucher.CurrentCell.Value = "" Then
                Me.GridVoucher.CurrentCell.Value = "0.00"
            Else
                Try
                    Me.GridVoucher.CurrentCell.Value = CDbl(Me.GridVoucher.CurrentCell.Value).ToString("N2")
                    If CDbl(Me.GridVoucher.CurrentCell.Value) <> 0 Then
                        Me.GridVoucher.CurrentRow.Cells(Me.DGColCredit.Index).Value = "0.00"
                    End If
                Catch ex As Exception
                    Me.GridVoucher.CurrentCell.Value = "0.00"
                End Try
            End If

        ElseIf e.ColumnIndex = Me.DGColCredit.Index Then
            If Me.GridVoucher.CurrentCell.Value = "" Then
                Me.GridVoucher.CurrentCell.Value = "0.00"
            Else
                Try
                    Me.GridVoucher.CurrentCell.Value = CDbl(Me.GridVoucher.CurrentCell.Value).ToString("N2")
                    If CDbl(Me.GridVoucher.CurrentCell.Value) <> 0 Then
                        Me.GridVoucher.CurrentRow.Cells(Me.DGColDebit.Index).Value = "0.00"
                    End If
                Catch ex As Exception
                    Me.GridVoucher.CurrentCell.Value = "0.00"
                End Try
            End If

        End If

        Calculate()
        ValidateGrid()
    End Sub

    Sub ValidateGrid()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridVoucher.Rows
                If row.Cells(4).Value = "" Then
                    row.Cells(4).Style.BackColor = Color.Salmon
                Else
                    row.Cells(4).Style.BackColor = row.DefaultCellStyle.BackColor
                End If

                If CDbl(row.Cells(9).Value) = 0 And CDbl(row.Cells(10).Value) = 0 Then
                    row.Cells(9).Style.BackColor = Color.Salmon
                    row.Cells(10).Style.BackColor = Color.Salmon

                ElseIf CDbl(row.Cells(9).Value) = 0 Or CDbl(row.Cells(10).Value) = 0 Then
                    row.Cells(9).Style.BackColor = row.DefaultCellStyle.BackColor
                    row.Cells(10).Style.BackColor = row.DefaultCellStyle.BackColor
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Sub Calculate()
        If Me.GridVoucher.Rows.Count = 0 Then
            Me.txtCrd.Text = "0"
            Me.txtDep.Text = "0"
            Me.txtBalance.Text = "0"
        Else
            Try
                Dim Crd, Dep As Double
                Dim i As Integer

                ' Iterate through a dictionary
                For i = 0 To Me.GridVoucher.Rows.Count - 1
                    Dep = Dep + Me.GridVoucher.Rows(i).Cells(Me.DGColDebit.Index).Value
                    Crd = Crd + Me.GridVoucher.Rows(i).Cells(Me.DGColCredit.Index).Value
                Next

                If Crd = 0 Then
                    Me.txtCrd.Text = "0.00"
                Else
                    Me.txtCrd.Text = Format(Crd, "##,###.##")
                End If

                If Dep = 0 Then
                    Me.txtDep.Text = "0.00"
                Else
                    Me.txtDep.Text = Format(Dep, "##,###.##")
                End If

                If Crd - Dep = 0 Then
                    Me.txtBalance.Text = "0.00"
                Else
                    Me.txtBalance.Text = Format(CDbl(Crd - Dep), "##,###.##")
                End If
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub BtnReject_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnReject.Click
        If MsgBox("Confirm rejection?", MsgBoxStyle.YesNo) = DialogResult.Yes Then
            Dim newName As String = ""
            newName = InputBox("Enter the rejection reason:", "Rejection reason", "")
            If Not String.IsNullOrEmpty(newName) Then
                RejectionInfo.Add(newName)
            End If

            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Update Policies Set RejectionInfo=N'" & newName & "',Done=0 Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)

                cnn.Open()
                cmd.ExecuteNonQuery()
                cnn.Close()

                MsgBox("Rejected Successully!")

                FillPendingTransactions()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

End Class